skrypt sql i srypt gnuplot

Witam ponownie drodzy Państwo, to znowu ja :D Ostatnio dość długo męczyłem z Waszą wielką pomocą XML'e i z sukcesem, zaliczone na 4+, więc przy okazji wielkie dzięki za pomoc.
Dzisiaj mam zmierzysz się z kolejnym problem, który z początku wydawał mi się prosty, lecz gdy się zagłębiłem trochę w temat już tak różowo nie jest. Mianowicie mam napisać skypty, jeden ma generować wykres liniowy obrazujący liczbę obsłużonych pacjentów w danym roku ( z rodzdzielczością 1 miesiąca) przez lekarzy pracujących w przychodni. Ma to wyglądać tak że najpierw ma być odpalany skrypt generujący dane, następnie skrypt gnuplot który wytworzy wykres. Dla każdego lekarza powinna być osobna linia na tymże wykresie. Oczywiście należy sparametryzować te skrypty, aby można było je wykorzystać dla wybranych lat. Skrypt SQL powinien być odpalony w sqlcmd.
Tabele zaprojektowane według następującego schematu:

 create table Pacjenci (
Id int primary key,
Imie varchar(25),
Nazwisko varchar(25))

create table Lekarze(
Id int primary key,
Imie varchar(25),
Nazwisko varchar(25)
)

create table Terminy(
Id int primary key,
IdLekarza int foreign key references Lekarze(Id),
OdGodziny Time,
DoGodziny Time,
Dzien Date)


create table Wizyty(
Id int primary key,
IdPacjenta int foreign key references Pacjenci(Id),
IdTerminu int foreign key references Terminy(Id),
Godzina  Time
)

napisałem też selecta, tylko tyle, że mój select nie zakłada tego podziału właśnie na miesięczne okresy ;/

 SELECT
Lekarze.Imie
,Lekarze.Nazwisko
,count(Wizyty.Id) ile
FROM 
(Lekarze join Terminy on Lekarze.Id=Terminy.IdLekarza) join 
Wizyty on Wizyty.IdTerminu=Terminy.Id
WHERE
Terminy.Dzien BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY
Lekarze.Imie, Lekarze.Nazwisko

prowadzący podał jeszcze nam takie polecenia jak niżej, ale na razie to brak pomysłu jak z tym ruszyć do przodu

 sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -i "K:\LAB06\select.txt" > select.tmp
type select.tmp | findstr /r /v ^\-[,\-]*$>select.csv

Nie pracowałem na sql server express (?), ale powinieneś pogrupować te dane wykorzystując funkcje YEAR oraz MONTH . Zreszta w dokumentacji masz przykład (C) zapytania grupującego po roku: https://msdn.microsoft.com/en-us/library/ms177673.aspx

wygląda już lepiej :D

 SELECT
 Year(Terminy.Dzien) AS [Year],
 Month(Terminy.Dzien) AS [Month],
Lekarze.Imie
,Lekarze.Nazwisko
,COUNT(Wizyty.Id) ile
FROM 
(Lekarze JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza) JOIN 
Wizyty ON Wizyty.IdTerminu=Terminy.Id
WHERE
Terminy.Dzien BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY
Year(Terminy.Dzien),Month(Terminy.Dzien),Lekarze.Imie, Lekarze.Nazwisko

no to dodaj jeszcze zmienną roku:

skrypt sql;

set nocount on
go
SELECT
	Lekarze.Imie
	,Lekarze.Nazwisko
	,COUNT(Wizyty.Id) ile
FROM 
	Lekarze 
	JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza 
	JOIN Wizyty ON Wizyty.IdTerminu=Terminy.Id
WHERE
year(Terminy.Dzien) = $(rok)
GROUP BY
	Lekarze.Imie
	,Lekarze.Nazwiso

sqlcmd:

sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -i "K:\LAB06\select.txt" -s"," -W -v rok="2016"> SELECT.tmp
TYPE SELECT.tmp | findstr /r /v ^\-[,\-]*$>SELECT.csv 

-s"," - rozdzielenie kolumn przecinkami
-W - kolumny nie są wyrównywane spacjami
-v - przekazanie zmiennej do skryptu

teraz pozostała kwestia tego iż ten skrypt wywali mi wszystkie dane do jednego pliku, a jak będę robił wykres w tym gnuplot'cie to tam muszę podać tak jakby jeden plik dla jednego lekarza, więc ten skrypt muszę mieć w takiej postaci, iż zwróci mi kilka plików, tyle ile jest lekarzy, dla każdego lekarza jeden plik, w którym właśnie będą przechowywane dane o tym ile pacjentów obsłużył w danym poszczególnych miesiącach
poprawiłem tego select'a, teraz mam w takiej formie

 SELECT
 Year(Terminy.Dzien) AS [Year],
 Month(Terminy.Dzien) AS [Month],
Lekarze.Imie
,Lekarze.Nazwisko
,COUNT(Wizyty.Id) ile
FROM 
(Lekarze JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza) JOIN 
Wizyty ON Wizyty.IdTerminu=Terminy.Id
WHERE
YEAR(Terminy.Dzien) = $(rok)
GROUP BY
Year(Terminy.Dzien),Month(Terminy.Dzien),Lekarze.Imie, Lekarze.Nazwisko

No to musisz pobrać listę lekarzy i puscić dla kazdego osobno, np coś w ten deseń:

SET nocount ON
GO
SELECT
 YEAR(Terminy.Dzien) AS [YEAR],
 MONTH(Terminy.Dzien) AS [MONTH],
Lekarze.Imie
,Lekarze.Nazwisko
,COUNT(Wizyty.Id) ile
FROM 
(Lekarze JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza) JOIN 
Wizyty ON Wizyty.IdTerminu=Terminy.Id
WHERE
YEAR(Terminy.Dzien) = $(rok)
and Lekarze.Id=$(lekarz)
GROUP BY
YEAR(Terminy.Dzien),MONTH(Terminy.Dzien),Lekarze.Imie, Lekarze.Nazwisko

I skrypt plot.bat:

 @echo off
set rok=%1
REM CZYTAJ LEKARZY
echo Tworzenie listy lekarzy
sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -Q "set nocount on; select distinct id from lekarze"  -o K:\LAB06\outlist.csv -s"," -h-1 -W
for /f "tokens=*" %%a in (K:\LAB06\outlist.csv) do (
	set var=%%a&call :tworzenieCSV
)
:tworzenieCSV
set lekarz=%var%
echo     Pobieranie danych dla: %lekarz%
sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -i "K:\LAB06\select.txt" -s"," -W -v rok="%rok%" lekarz="%lekarz%" > SELECT.tmp
TYPE SELECT.tmp | findstr /r /v ^\-[,\-]*$>lekarz%lekarz%.csv 
goto :eof 

:eof

Wywołanie:

plot.bat 2016

bez tego plot.bat nie da rady ? ;/ bo nie robiliśmy nic takiego, więc nie bardzo wiem co tam się dzieje

no da radę, to w końcu jest zwykły bat, ale piszesz, ze musisz genrowac dla kazdego lekarza ręcznie, więc tu nie ma żadnej magi

set rok=%1

To przypisanie przekazanego parametru do zmiennej rok, wykorzystanej do puszczenia skryptu.

sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -Q "set nocount on; select distinct id from lekarze"  -o K:\LAB06\outlist.csv -s"," -h-1 -W

To weźmie wszystkie id lekarzy i stworzy z nich plik outlist.csv

for /f "tokens=*" %%a in (K:\LAB06\outlist.csv) do (
    set var=%%a&call :tworzenieCSV
)

ta petla czyta wszystkie linijki z plike outlist.csv i dla każdej uruchamia kod napisany pod :tworzenieCSV

sqlcmd -S LAPTOP\SQLEXPRESS -d LAB05 -i "K:\LAB06\select.txt" -s"," -W -v rok="%rok%" lekarz="%lekarz%" > SELECT.tmp
TYPE SELECT.tmp | findstr /r /v ^\-[,\-]*$>lekarz%lekarz%.csv 

to znasz tylko dodalem zmienną lekarz, aby móc wykonać zapytanie dla każdego lekarza osobno, i zwróćić wynik do pliku lekarz{idlekarza}.csv

To, ze czegoś nie robiłes na zajęciach, nie znaczy że nie możesz wykorzystać, skoro korzystasz z cmd to dlaczego tego nie użyć, alternatywa to ręczne pisanie sqlcmd dla każdego lekarza...

Racja, dzięki wielkie mistrzu, a noz widelec nawet zwolni z egzaminu za zakres poza materiał jutro to będę testował, więc jak coś to będę pytał

Dzisiaj podczas testów pojawił mi się taki problem w głowie, co w sytuacji gdy będę już w gnuplocie generował wykresy dla tych wszystkich plików. Skoro w nich mam tylko wypisane miesiące, w których były jakiekolwiek wizyty u danego lekarza, a ja do wykresu liniowego potrzebuję również danych czyli zer w miesiącach, w których lekarz nie miał żadnej wizyty. Ponadto chciałbym w jakiś sposób pozbyć się z tego pliku .csv informacji w ostatnim wierszu o tym że dodano tyle i tyle wierszy, gdyż przy generowaniu tego wykresu może to powodować problem. Pozdrawiam

musisz przygotować odpowiednio dane, najlepiej tabela tymczasowa z wartościami od 1-12 i lef join z wynikiem.
Z nie zwracanie row affected służy pierwsza linjka set nocount on

Przykład:

set nocount on
go
create table #m (
 m int)
 go
 insert into #m
 select count(*)+1 from #m
 go 12
 select 
	m
	,isnull(w.ile,0) ile 
from 
	#m m
	left join (SELECT
					 MONTH(Terminy.Dzien) AS [M],
					,COUNT(Wizyty.Id) ile
				FROM 
					Lekarze 
					JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza
					JOIN Wizyty ON Wizyty.IdTerminu=Terminy.Id
				WHERE
					YEAR(Terminy.Dzien) = $(rok)
					AND Lekarze.Id=$(lekarz)
				GROUP BY
					MONTH(Terminy.Dzien)) w on w.m=m.m
 go
 drop table #m
 go

ma ktoś pomysł jak zrobić taką pętlę ??

 set key autotitle columnhead
set datafile separator ","
set xlabel 'Miesiąc'
set ylabel 'Liczba'
plot for [i=1:20] "K:\\LAB06\\lekarz%i.csv" using 1:2:xtic(1) with lines

aaa i jeszcze pytanie do poprzedniego selecta, da radę dodać jeszcze dane lekarza, czyli imię i nazwisko lekarza? bo mi wyskakuje taki błąd, jak dodałem w select te dwie kolumny

 Column 'Lekarze.Imie' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Komunikat jest jasny dodałes kolumne w selekcie, ale nie w grupowaniu...

SET nocount ON
GO
CREATE TABLE #m (
 m INT)
 GO
 INSERT INTO #m
 SELECT COUNT(*)+1 FROM #m
 GO 12
 SELECT 
    m
    ,isnull(w.ile,0) ile 
    ,lekarz
FROM 
    #m m
    LEFT JOIN (SELECT
                     MONTH(Terminy.Dzien) AS [M]
                    ,COUNT(Wizyty.Id) ile
                    ,lekarze.imie + ' ' + lekarze.Nazwisko Lekarz
                FROM 
                    Lekarze 
                    JOIN Terminy ON Lekarze.Id=Terminy.IdLekarza
                    JOIN Wizyty ON Wizyty.IdTerminu=Terminy.Id
                WHERE
                    YEAR(Terminy.Dzien) = $(rok)
                    AND Lekarze.Id=$(lekarz)
                GROUP BY
                    MONTH(Terminy.Dzien)
                    ,lekarze.imie + ' ' + lekarze.Nazwisko
      ) w ON w.m=m.m
 GO
 DROP TABLE #m
 GO

i tak coś nie trybi mi ...
w linijce z grupowaniem gdzie mam to imie i nazwisko wyskakuje taki błąd
Incorrect syntax near '+'.

Poza tym, że zrobilem literówkę i imię napisałem przez n nie widzę błędu.

Poprawiłem post ze skrypterm, był tam nadmiarowy przecinek po [m]

sorry za zamieszanie, ale już ślepy chyba jestem, nie zauważyłem że w tym zewnętrznym select, też musi być lekarz, dzięki wielkie